from datetime import datetime
import pandas as pd
from openpyxl.styles import numbers
from openpyxl.utils import get_column_letter

from bct.tools.client.bct4_client import BCT4Client, BCTVer
from bct_config import Bct4Hoster

# 分页参数, 查询第一页, 共 pageSize 条记录
page = 0
pageSize = 1000000

# 日志查询时间范围, 建议时间跨度不要太大
start_date_time = '2022-04-01 00:00:00'
end_date_time = '2022-04-29 00:00:00'
# 需要统计的事件类型, 直接填前端下拉框内的中文即可
operations = ['用户-登录', '用户-登出', '场外期权-簿记交易', '场外期权-导入交易', '场外期权-修改交易', '场外期权-删除交易',
              '交易对手-一段式新增', '交易对手-两段式新增', '交易对手-修改（需审批）', '交易对手-修改(默认)', '交易对手-导入', '交易对手-批量修改']


def get_operation_log():
    params = {"operationTimeFrom": start_date_time,
              "operationTimeTo": end_date_time,
              "operations": operations,
              "page": page,
              "pageSize": pageSize}
    return client.find_operation_log(params)


def get_empty_stat(username, nickname, role):
    stat = {"用户": username, "用户昵称": nickname, "用户岗位": role}
    for operation in operations:
        stat[operation] = 0
    return stat


def operation_statistics():
    print('获取操作日志')
    rsp = get_operation_log()
    logs = rsp.get("page")
    print('共获取到'+str(len(logs))+'条操作日志')
    user_list = client.find_user_list()
    username_list = [user.get("username") for user in user_list]
    user_map = dict(zip(username_list, user_list))

    statistics_logs = []
    for log in logs:
        if user_map.get(log.get("userName")) is not None:
            statistics_logs.append({"userName": log.get("userName"), "operation": log.get("operation")})

    df = pd.DataFrame(statistics_logs)
    gp = df.groupby(by=['userName', 'operation'])
    print('正在统计操作日志')
    stat_dict = {}
    for gn, v in gp:
        username = gn[0]
        user = user_map.get(username)
        nickname = '' if user is None else user.get("nickName")
        role_list = [] if user is None else user.get("roleName")
        op = gn[1]
        op_count = len(v)
        if stat_dict.get(username) is None:
            stat_dict[username] = get_empty_stat(username, nickname, ','.join(role_list))
        stat_dict.get(username)[op] = op_count

    stat_df = pd.DataFrame(stat_dict.values())

    filename = 'BCT4操作日志导出_{}.xlsx'.format(datetime.now().strftime("%Y-%m-%d"))
    writer = pd.ExcelWriter(filename, engine='openpyxl')  # , engine='xlsxwriter'
    stat_df.to_excel(writer, index=False)
    workbook = writer.book
    ws = workbook.active
    # 格式调整
    __init_format(stat_df, ws)
    writer.save()
    print('导出操作日志成功, 文件名:'+filename)


def __init_format(df, ws):
    col_list = df.columns.to_list()
    for i in range(len(col_list)):
        ws.column_dimensions[get_column_letter(i + 1)].width = 18


def __col_width(df, ws, title, col_width):
    col_list = df.columns.to_list()
    if col_list.index(title) < 0:
        return
    ws.column_dimensions[get_column_letter(col_list.index(title) + 1)].width = col_width


if __name__ == '__main__':
    config = Bct4Hoster()
    client = BCT4Client(config.host, config.script_user, config.script_pwd, BCTVer.V_42)
    operation_statistics()
